/**
 * 
 */
package dstation.model;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import dstation.databeans.SensorBean;
import dstation.databeans.TestBean;
import dstation.testbeans.TestMetadataBean;

/**
 * @author Evan
 *
 */
public class TestDAO {
	private static String insertString;
	private static String lookupString;
	private static String updateString;
	private static String tableString;

	/**
	 * Instantiate a new Test Data Access Object
	 * @param tableName The name of the Test table
	 */
	public TestDAO(String tableName){
		insertString = "insert into "+tableName+" (name, type, description, metadata) values ( ?, ?, ?, ?)";
		lookupString = "select * from "+tableName+" where testid = ?";
		updateString = "update "+tableName+" set name = ? , type = ? , description = ? , metadata = ? where testid = ?";
		tableString = "select testid, name, type, description from "+tableName;
		try {
			Connection con = DriverManager.getConnection("jdbc:mysql:///colony");
			ResultSet tableNames = con.prepareStatement("SHOW TABLES").executeQuery();
			boolean exists = false;
			while(tableNames.next()){
				String temp = tableNames.getString(1);
				if(temp.equals(tableName)){
					exists = true;
					break;
				}
			}
			if(!exists){
				Statement stmt = con.createStatement();
				stmt.executeUpdate("create table "+tableName+" (testid int not null auto_increment," +
						" name varchar(100) not null, type enum('BOM','ENCODER','RF') not null, description varchar(255), metadata blob," +
				" primary key(testid), unique (name))");
				stmt.close();
			}
			tableNames.close();
			con.close();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	/**
	 * Create a new test
	 * @param t A bean containing data about the test to be created (id is ignored)
	 * @return The bean that was stored
	 * @throws SQLException 
	 * @throws IOException 
	 */
	public TestBean insert(TestBean t) throws SQLException, IOException{
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = DriverManager.getConnection("jdbc:mysql:///colony");
			con.setAutoCommit(false);
			stmt = con.prepareStatement(insertString, Statement.RETURN_GENERATED_KEYS);
			stmt.setString(1, t.getName());
			stmt.setString(2, t.getType().toString());
			stmt.setString(3, t.getDescription());


			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			ObjectOutputStream oos = new ObjectOutputStream(baos);
			oos.writeObject(t.getMetadata());
			// serialize the employee object into a byte array
			byte[] dataAsBytes = baos.toByteArray();
			ByteArrayInputStream bais = 
				new ByteArrayInputStream(dataAsBytes);
			// bind our byte array  to the emp column
			stmt.setBinaryStream(4,bais, dataAsBytes.length);

			stmt.executeUpdate();
			con.commit();
			ResultSet rs = stmt.getGeneratedKeys();
			rs.next();
			t.setTestid(rs.getInt(1));
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if(stmt!=null){stmt.close();}
			if(con!=null){con.close();}

		}
		return t;
	}

	/**
	 * Retrieve a Test from the database
	 * @param id The Test to retrieve
	 * @return The retrieved Test
	 * @throws Exception 
	 */
	public TestBean lookup(int id) throws Exception{
		Connection con = null;
		PreparedStatement stmt = null;
		TestBean t = new TestBean();
		try {
			con = DriverManager.getConnection("jdbc:mysql:///colony");
			con.setAutoCommit(false);
			stmt = con.prepareStatement(lookupString);
			stmt.setInt(1, id);
			ResultSet rs = stmt.executeQuery();
			rs.next();
			con.commit();
			t.setTestid(rs.getInt("testid"));
			t.setName(rs.getString("name"));
			t.setDescription(rs.getString("description"));
			t.setType(SensorBean.SensorType.valueOf(rs.getString("type")));

			byte[] st = (byte[])rs.getObject("metadata");
			//   or  byte[] st = rs.getBytes(1);
			//   or  Blob aBlob = rs.getBlob(1);
			//       byte[] st = aBlob.getBytes(0, (int) aBlob.length());
			ByteArrayInputStream baip = 
				new ByteArrayInputStream(st);
			ObjectInputStream ois = 
				new ObjectInputStream(baip);
			// re-create the object
			t.setMetadata((TestMetadataBean) ois.readObject());


		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} catch (Exception e){
			e.printStackTrace();
			throw e;
		} finally {
			if(stmt!=null){stmt.close();}
			if(con!=null){con.close();}

		}
		return t;
	}

	/**
	 * Modify a test in the database
	 * @param t A bean containing data about the test to be modified
	 * @throws SQLException 
	 * @throws IOException 
	 */
	public void update(TestBean t) throws SQLException, IOException{
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = DriverManager.getConnection("jdbc:mysql:///colony");
			con.setAutoCommit(false);
			stmt = con.prepareStatement(updateString);
			stmt.setString(1, t.getName());
			stmt.setString(2, t.getType().toString());
			stmt.setString(3, t.getDescription());
			
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			ObjectOutputStream oos = new ObjectOutputStream(baos);
			oos.writeObject(t.getMetadata());
			// serialize the employee object into a byte array
			byte[] dataAsBytes = baos.toByteArray();
			ByteArrayInputStream bais = 
				new ByteArrayInputStream(dataAsBytes);
			// bind our byte array  to the emp column
			stmt.setBinaryStream(4,bais, dataAsBytes.length);

			
			
//			Blob myBlob = con.createBlob();
//			ObjectOutputStream blobWriter = new ObjectOutputStream(new BufferedOutputStream(myBlob.setBinaryStream(1)));
//			blobWriter.writeObject(t.getMetadata());
//			stmt.setBlob(4, myBlob);
			stmt.setInt(5, t.getTestid());
			stmt.executeUpdate();
			con.commit();
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if(stmt!=null){stmt.close();}
			if(con!=null){con.close();}
		}
	}

	public List<TestBean> getTestTable() throws SQLException {
		Connection con = null;
		PreparedStatement stmt = null;
		List<TestBean> list = new ArrayList<TestBean>();
		try {
			con = DriverManager.getConnection("jdbc:mysql:///colony");
			con.setAutoCommit(false);
			stmt = con.prepareStatement(tableString);
			ResultSet rs = stmt.executeQuery();
			con.commit();
			while(rs.next()){
				TestBean t = new TestBean();
				t.setName(rs.getString("name"));
				t.setDescription(rs.getString("description"));
				t.setType(SensorBean.SensorType.valueOf(rs.getString("type")));
				t.setTestid(rs.getInt("testid"));
				list.add(t);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw e;
		} finally {
			if(stmt!=null){stmt.close();}
			if(con!=null){con.close();}
		}
		return list;
	}
}
